﻿Imports System.Data.SQLite

Public Class ClsDatabase

    Public Const SQLSource As String = "bandyt.sync"

    ''' <summary>
    ''' Initializes the database and creates the tables (if not existing)
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub InitDB()
        Dim SQLConnect As New SQLiteConnection
        Dim SQLCommand As SQLiteCommand
        Dim SQL As String

        SQLConnect.ConnectionString = "Data Source=" & SQLSource & ";"

        SQLConnect.Open()
        SQLCommand = SQLConnect.CreateCommand
        SQL = "CREATE  TABLE IF NOT EXISTS `bs_jobs` ("
        SQL = SQL & "`id` INTEGER PRIMARY KEY AUTOINCREMENT ,"
        SQL = SQL & "`name` TEXT NOT NULL DEFAULT '',"
        SQL = SQL & "`guid` CHAR(36) NOT NULL DEFAULT '',"
        SQL = SQL & "`description` LONGTEXT NOT NULL DEFAULT '',"
        SQL = SQL & "`connection_mode` CHAR(4) NOT NULL DEFAULT '',"
        SQL = SQL & "`sync_mode` CHAR(4) NOT NULL DEFAULT '',"
        SQL = SQL & "`folder_left` TEXT NOT NULL DEFAULT '',"
        SQL = SQL & "`folder_right` TEXT NOT NULL DEFAULT ''"
        SQL = SQL & ");"
        SQLCommand.CommandText = SQL
        SQLCommand.ExecuteNonQuery()
        SQLCommand.Dispose()

        SQLConnect.Close()


    End Sub

    ''' <summary>
    ''' Performs a query and returns the result
    ''' </summary>
    ''' <param name="Command">The SQL query. Most likely a SELECT statement</param>
    ''' <returns>The result of the query as SQLiteDataReader</returns>
    ''' <remarks></remarks>
    Private Function QueryDB(ByVal Command As String) As SQLiteDataReader
        Dim SQLConnect As New SQLiteConnection
        Dim SQLCommand As SQLiteCommand
        Dim SQLReader As SQLiteDataReader

        SQLConnect.ConnectionString = "Data Source=" & SQLSource & ";"
        SQLConnect.Open()
        SQLCommand = SQLConnect.CreateCommand
        SQLCommand.CommandText = Command
        SQLReader = SQLCommand.ExecuteReader
        Return SQLReader
        SQLCommand.Dispose()
        SQLConnect.Close()
    End Function

    ''' <summary>
    ''' Performs an INSERT or UPDATE statement
    ''' </summary>
    ''' <param name="Command">The INSERT or UPDATE statement to be executed</param>
    ''' <remarks></remarks>
    Private Sub InsertIntoDB(ByVal Command As String)
        Dim SQLConnect As New SQLiteConnection
        Dim SQLCommand As SQLiteCommand

        SQLConnect.ConnectionString = "Data Source=" & SQLSource & ";"
        SQLConnect.Open()
        SQLCommand = SQLConnect.CreateCommand
        SQLCommand.CommandText = Command
        SQLCommand.ExecuteNonQuery()
        SQLCommand.Dispose()
        SQLConnect.Close()
    End Sub

    ''' <summary>
    ''' Retrieves the jobs on the database and returns them
    ''' </summary>
    ''' <returns>The id and name of the jobs on the database</returns>
    ''' <remarks></remarks>
    Public Function getJobs() As Collection
        Dim SQLReader As SQLiteDataReader
        Dim Jobs As New Collection

        SQLReader = QueryDB("SELECT id,name FROM bb_jobs")
        While SQLReader.Read
            If IsDBNull(SQLReader("id")) = False Then
                Dim Job(1) As String
                Job(0) = SQLReader("id")
                Job(1) = SQLReader("name")
                Jobs.Add(Job)
            End If
        End While
        Return Jobs
    End Function

    ''' <summary>
    ''' Adds a new job to the database
    ''' </summary>
    ''' <param name="JobTitle">Title or name of the job</param>
    ''' <param name="JobDescription">Longtext field for describing details of the job</param>
    ''' <remarks></remarks>
    Public Sub AddJob(ByVal JobTitle, ByVal JobDescription)
        Dim GUID As String

        GUID = System.Guid.NewGuid.ToString
        InsertIntoDB("INSERT INTO bb_jobs (name,guid,description) VALUES ('" & JobTitle & "','" & GUID & "','" & JobDescription & "')")
    End Sub
End Class
